package com.example.simpleforum.dao;

import com.example.simpleforum.model.ForumApplication;
import com.example.simpleforum.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ForumApplicationDao {
    
    /**
     * 创建板块申请
     */
    public int createApplication(ForumApplication application) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int applicationId = -1;
        
        try {
            conn = DBUtil.getConnection();
            
            // 根据是申请管理已有板块还是申请创建新板块，使用不同的SQL
            String sql;
            if (application.getForumId() != null) {
                // 申请管理已有板块
                sql = "INSERT INTO forum_application (forum_id, applicant_id, reason) VALUES (?, ?, ?)";
                pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                pstmt.setInt(1, application.getForumId());
                pstmt.setInt(2, application.getApplicantId());
                pstmt.setString(3, application.getReason());
            } else {
                // 申请创建新板块
                sql = "INSERT INTO forum_application (forum_name, forum_description, applicant_id, reason) VALUES (?, ?, ?, ?)";
                pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                pstmt.setString(1, application.getForumName());
                pstmt.setString(2, application.getForumDescription());
                pstmt.setInt(3, application.getApplicantId());
                pstmt.setString(4, application.getReason());
            }
            
            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                rs = pstmt.getGeneratedKeys();
                if (rs.next()) {
                    applicationId = rs.getInt(1);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        
        return applicationId;
    }
    
    /**
     * 更新申请状态
     */
    public boolean updateApplicationStatus(int applicationId, ForumApplication.Status status, 
                                         int reviewerId, String reviewNotes) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        
        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE forum_application SET status = ?, reviewer_id = ?, " +
                         "review_notes = ?, review_time = CURRENT_TIMESTAMP WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, status.name());
            pstmt.setInt(2, reviewerId);
            pstmt.setString(3, reviewNotes);
            pstmt.setInt(4, applicationId);
            
            int affectedRows = pstmt.executeUpdate();
            success = (affectedRows > 0);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        
        return success;
    }
    
    /**
     * 根据ID获取申请
     */
    public ForumApplication findById(int id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ForumApplication application = null;
        
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM forum_application WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            
            if (rs.next()) {
                application = new ForumApplication();
                application.setId(rs.getInt("id"));
                
                // 处理可能为NULL的forum_id
                if (rs.getObject("forum_id") != null) {
                    application.setForumId(rs.getInt("forum_id"));
                }
                
                // 处理新增的字段
                application.setForumName(rs.getString("forum_name"));
                application.setForumDescription(rs.getString("forum_description"));
                
                application.setApplicantId(rs.getInt("applicant_id"));
                application.setReason(rs.getString("reason"));
                application.setStatus(ForumApplication.Status.valueOf(rs.getString("status")));
                application.setReviewerId(rs.getInt("reviewer_id"));
                application.setReviewTime(rs.getTimestamp("review_time"));
                application.setReviewNotes(rs.getString("review_notes"));
                application.setApplyTime(rs.getTimestamp("apply_time"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        
        return application;
    }
    
    /**
     * 获取用户的所有申请
     */
    public List<ForumApplication> findByApplicantId(int applicantId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<ForumApplication> applications = new ArrayList<>();
        
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM forum_application WHERE applicant_id = ? ORDER BY apply_time DESC";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, applicantId);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                ForumApplication application = new ForumApplication();
                application.setId(rs.getInt("id"));
                
                // 处理可能为NULL的forum_id
                if (rs.getObject("forum_id") != null) {
                    application.setForumId(rs.getInt("forum_id"));
                }
                
                // 处理新增的字段
                application.setForumName(rs.getString("forum_name"));
                application.setForumDescription(rs.getString("forum_description"));
                
                application.setApplicantId(rs.getInt("applicant_id"));
                application.setReason(rs.getString("reason"));
                application.setStatus(ForumApplication.Status.valueOf(rs.getString("status")));
                application.setReviewerId(rs.getInt("reviewer_id"));
                application.setReviewTime(rs.getTimestamp("review_time"));
                application.setReviewNotes(rs.getString("review_notes"));
                application.setApplyTime(rs.getTimestamp("apply_time"));
                applications.add(application);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        
        return applications;
    }
    
    /**
     * 获取待处理的申请
     */
    public List<ForumApplication> findPendingApplications() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<ForumApplication> applications = new ArrayList<>();
        
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM forum_application WHERE status = 'pending' ORDER BY apply_time ASC";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                ForumApplication application = new ForumApplication();
                application.setId(rs.getInt("id"));
                
                // 处理可能为NULL的forum_id
                if (rs.getObject("forum_id") != null) {
                    application.setForumId(rs.getInt("forum_id"));
                }
                
                // 处理新增的字段
                application.setForumName(rs.getString("forum_name"));
                application.setForumDescription(rs.getString("forum_description"));
                
                application.setApplicantId(rs.getInt("applicant_id"));
                application.setReason(rs.getString("reason"));
                application.setStatus(ForumApplication.Status.valueOf(rs.getString("status")));
                application.setReviewerId(rs.getInt("reviewer_id"));
                application.setReviewTime(rs.getTimestamp("review_time"));
                application.setReviewNotes(rs.getString("review_notes"));
                application.setApplyTime(rs.getTimestamp("apply_time"));
                applications.add(application);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        
        return applications;
    }
    
    /**
     * 检查用户是否已有待处理的申请（针对已有板块）
     */
    public boolean hasPendingApplication(int forumId, int applicantId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean exists = false;
        
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT 1 FROM forum_application WHERE forum_id = ? AND applicant_id = ? AND status = 'pending'";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, forumId);
            pstmt.setInt(2, applicantId);
            rs = pstmt.executeQuery();
            
            if (rs.next()) {
                exists = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        
        return exists;
    }
    
    /**
     * 检查用户是否已有待处理的创建板块申请
     */
    public boolean hasPendingCreateApplication(int applicantId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean exists = false;
        
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT 1 FROM forum_application WHERE forum_id IS NULL AND applicant_id = ? AND status = 'pending'";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, applicantId);
            rs = pstmt.executeQuery();
            
            if (rs.next()) {
                exists = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        
        return exists;
    }
    
    /**
     * 检查板块名称是否已有待处理的申请
     */
    public boolean hasPendingForumNameApplication(String forumName) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean exists = false;
        
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT 1 FROM forum_application WHERE forum_name = ? AND status = 'pending'";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, forumName);
            rs = pstmt.executeQuery();
            
            if (rs.next()) {
                exists = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        
        return exists;
    }
}